import xlwt
def readList(full_grade_list):
    '''
    返回一个题目组成的列表
    :param full_grade_list:c总返回的列表
    :return: 1.用于表头的格式【【平时成绩，【【期中，1.1，1.2，2.1】，【平时，1.1，1.2】】】，【期末成绩，【【】，【】】】】
             2.由于获取目标分值[[[100],[100]],[[100],[100],[100]]
    '''
    #读取表格sheet类型个数
    sheet_count=len(full_grade_list)
    topic_list=[]
    for i in range(sheet_count):
        temp=[full_grade_list[i][0]]
        topic_list.append(temp)
    #获取每一道题
    target_value_list=[]
    for i in range(sheet_count):
        temp1=[]
        temp2=[]
        length=0
        for j in range(3, len(full_grade_list[i]),4):
            big_topic_list=full_grade_list[i][j]
            alist=[big_topic_list[0]]
            blist = []
            for k in range(3,len(big_topic_list)):
                alist.append(big_topic_list[k][0])
                blist.append(big_topic_list[k][1])
            length+=(len(alist)-1)
            temp1.append(alist)
            temp2.append(blist)
        topic_list[i].append(temp1)
        topic_list[i].append(length)
        target_value_list.append(temp2)

    return topic_list,target_value_list

def setStyle(color_num,number):
    '''

    :param color_num:色号
    :param number: 数字型号
    :return:
    '''
    data_style=xlwt.XFStyle()

    # 居中
    cellalign=xlwt.Alignment()
    cellalign.horz=0x02
    cellalign.vert=0x01

    # 字体
    datafont=xlwt.Font()
    datafont.name='宋体'
    datafont.height=9*20

    #数字显示位数
    num_format_1 ='0'
    num_format_2='0.0'
    num_format=[0,num_format_1,num_format_2]

    #单元格颜色
    # 42黄绿色，31紫蓝色,52橘黄色
    pattern_list=[42,31,52]
    for i in range(3):
        pattern=xlwt.Pattern()
        pattern.pattern = xlwt.Pattern.SOLID_PATTERN
        pattern.pattern_fore_colour=pattern_list[i]
        pattern_list[i]=pattern

    # 设置边框
    borders=xlwt.Borders()
    borders.left = 1
    borders.right = 1
    borders.top = 1
    borders.bottom = 1

    data_style.alignment=cellalign
    data_style.font=datafont
    data_style.borders=borders
    data_style.pattern=pattern_list[color_num]
    if number!=0:
        data_style.num_format_str=num_format[number]
    return data_style

def creatEmptyExcel(title,class_info,topic_list):
    # 创建新表
    wb = xlwt.Workbook(encoding='utf-8', style_compression=0)
    #sheet的名字
    sheet_count=len(topic_list)
    sheet_name=[]
    for i in range(sheet_count):
        sheet_name.append(topic_list[i][0])
    ws=[]
    for i in sheet_name:
        temp=wb.add_sheet(i,cell_overwrite_ok=True)
        ws.append(temp)
    # 修改样式
    title_style = xlwt.XFStyle()
    info_style = xlwt.XFStyle()

    # 居中
    cellalign = xlwt.Alignment()
    cellalign.horz = 0x02
    cellalign.vert = 0x01

    # 标题字体
    titlefont = xlwt.Font()
    titlefont.name = '黑体'
    titlefont.bold = True
    titlefont.height = 13 * 20

    # 课程信息字体
    infofont = xlwt.Font()
    infofont.name = '宋体'
    # infofont.bold=True
    infofont.height = 11 * 20

    title_style.alignment = cellalign
    title_style.font = titlefont
    info_style.alignment = cellalign
    info_style.font = infofont

    tall_style1 = xlwt.easyxf('font:height 520')  # 设置行高
    tall_style2 = xlwt.easyxf('font:height 420')  # 设置行高

    col_index=[]
    for i in range(sheet_count):
        col_index.append(topic_list[i][-1])

    for i in range(sheet_count):
        ws[i].write_merge(0, 0, 0,col_index[i]+12,title,title_style)
        ws[i].row(0).set_style(tall_style1)
        ws[i].write_merge(1, 1, 0,col_index[i]+12,class_info,info_style)
        ws[i].row(1).set_style(tall_style2)
    return wb

def makeExcelTitle(title,class_info,topic_list,target_list):
    wb=creatEmptyExcel(title,class_info,topic_list)
    # 先创建样式
    sheet_count=len(topic_list)
    atype = setStyle(0, 0)
    btype = setStyle(0, 1)
    ctype = setStyle(2, 0)
    num_han = '一二三四五六七八九十'
    right_start = 13
    right_end=[]
    ws=[]
    for i in range(sheet_count):
        right_end.append(topic_list[i][-1]+right_start-1)
        ws.append(wb.get_sheet(i))
    table_fixed_list=['学号','姓名','课程编号','考试科目','考试状态','性别','是否留学生','成绩','学院','考试日期','班级','年级','备注']
    # 共同部分
    for i in range(sheet_count):
        ws[i].write_merge(2, 2, right_start, right_end[i], '试卷得分', atype)
        for index,item in enumerate(table_fixed_list):
            ws[i].write_merge(2,4,index,index,item,atype)

        ws[i].write_merge(5,5,0,len(table_fixed_list)-1,'目标分值',btype)

        flag=right_start
        for index,item in enumerate(topic_list[i][1]):
            length=len(item)-1
            ws[i].write_merge(3,3,flag,flag+length-1,'%s.%s'%(num_han[index],item[0]),atype)
            flag=flag+length

        flag = right_start
        for  item in topic_list[i][1]:
            alist=item[1:]
            for j in range(len(alist)):
                ws[i].write(4,flag+j,alist[j],ctype)
            flag=flag+len(alist)

        flag = right_start
        for j in range(len(target_list[i])):
            ws[i].write(5, flag,str(target_list[i][j][0]) ,ctype)
            flag = flag+1
    return wb

# import json
# path='tree.json'
# f = open(path)
# m = json.load(f)
# if __name__=='__main__':
#     title='2018-2019第二学期 钢结构设计原理 试卷分析表'
#     class_info='课程：[CE30108]钢结构设计原理（建工、地下、安装方向）        上课班级：208211-002              任课教师：熊刚'
#     topic_list=readList(m)[0]
#     target_list=readList(m)[1]
#     wb=makeExcelTitle(title,class_info,topic_list,target_list)
#     wb.save('成绩录入表.xls')